package cs5226.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import cs5226.model.*;

public class BufferCacheDAO extends AWRStatisticsDAO {
	public static List<SingleValueStatistics> getBufferCacheStatistics()
			throws SQLException {
		String exprStat = "1 - ((sum(decode(a.stat_name,'physical reads cache', a.value)) / " +
				" (sum(decode(a.stat_name,'consistent gets from cache', a.value)) + " +
				" sum(decode(a.stat_name,'db block gets from cache', a.value)) )))";
		String awrTable = "DBA_HIST_SYSSTAT";
		String filter = "a.stat_name in ('physical reads cache', 'consistent gets from cache', 'db block gets from cache')";
		
		return searchAWRStatistics(SingleValueStatistics.class, exprStat, awrTable, DAYSLATEST, filter);
	}

	public static List<SingleValueStatistics> getBufferCacheStatistics(String fromDate, String toDate)
			throws SQLException {
		String exprStat = "1 - ((sum(decode(a.stat_name,'physical reads cache', a.value)) / " +
				" (sum(decode(a.stat_name,'consistent gets from cache', a.value)) + " +
				" sum(decode(a.stat_name,'db block gets from cache', a.value)) )))";
		String awrTable = "DBA_HIST_SYSSTAT";
		String filter = "a.stat_name in ('physical reads cache', 'consistent gets from cache', 'db block gets from cache')";
		
		return searchAWRStatisticsWithinAPeriod(fromDate, toDate, SingleValueStatistics.class, exprStat, awrTable, DAYSLATEST, filter);
	}

	public static List<SingleValueStatistics> getBufferCacheStatisticsLatest()
			throws SQLException {
		String exprStat = "1 - ((sum(decode(a.stat_name,'physical reads cache', a.value)) / " +
				" (sum(decode(a.stat_name,'consistent gets from cache', a.value)) + " +
				" sum(decode(a.stat_name,'db block gets from cache', a.value)) )))";
		String awrTable = "DBA_HIST_SYSSTAT";
		String filter = "a.stat_name in ('physical reads cache', 'consistent gets from cache', 'db block gets from cache')";
		
		return searchAWRStatisticsLatest(SingleValueStatistics.class, exprStat, awrTable, DAYSLATEST, filter);
	}
}
